package com.netbliz.ftpmanagment.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;

import com.netbliz.common.dao.BaseDao;
import com.netbliz.common.util.PageBean;
import com.netbliz.ftpmanagment.pojo.StatUser;

public class StatUserDao extends BaseDao<StatUser, Integer>{

	public PageBean queryForPageDate(int page, int pageSize, String condition) {
		PageBean pageBean = getPageBean(condition, null, "", page, pageSize);
//		List list = jdbcTemplate.queryForList("SELECT _user,count(ID) record_num,sum(conn) conn,sum(down_file) down_file,sum(down_mb) down_mb,sum(up_file) up_file,sum(up_mb) up_mb FROM stat_user GROUP BY _user ORDER BY down_mb DESC");
		StringBuilder hql = new StringBuilder();
		hql.append("SELECT obj.user,sum(obj.conn) AS conn,sum(obj.downFile) AS downFile,sum(obj.downMb) AS downMb,sum(obj.upFile) AS upFile,sum(obj.upMb) AS upMb");
		hql.append(" " + FROM + getEntityClass().getName() + " obj");
		if (condition != null && !condition.trim().equals("")) {
			hql.append(WHERE + condition);
		}
		hql.append(" GROUP BY obj.user ORDER BY sum(obj.downMb) DESC");
//		hql.append(" GROUP BY user");
//		Query query = createQuery(getSession(), hql.toString(), null);
//		query.setFirstResult(page <= 0 ? 0 : pageSize * (page - 1));
//		query.setMaxResults(pageSize);
		
//		List list = query.list();
		List list = findByHql(hql.toString());
		List<StatUser> statUserList = new ArrayList<StatUser>();
		for(Object o : list) {
			Object[] obj = (Object[])o;
			StatUser su = new StatUser();
			su.setUser(obj[0].toString());
			su.setConn(Integer.parseInt(obj[1].toString()));
			su.setDownFile(Integer.parseInt(obj[2].toString()));
			su.setDownMb(Double.parseDouble(obj[3].toString()));
			su.setUpFile(Integer.parseInt(obj[4].toString()));
			su.setUpMb(Double.parseDouble(obj[5].toString()));
			statUserList.add(su);
		}
//		pageBean.setPageSize(pageSize);
//		pageBean.setCurrentPage(currentPage);
//		pageBean.setTotalPage(totalPage);
//		pageBean.setList(list);
		return PageBean.packagePageBean(pageSize,page,statUserList,statUserList.size());
	}
	
	/**
	 * 批量插入
	 * @param statUserList
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	public int[] batchInsert(List<StatUser> statUserList, Timestamp startTime, Timestamp endTime) {
		logger.info("Delete from stat_user from " + startTime + " to " + endTime);
		jdbcTemplate.execute("DELETE FROM stat_user WHERE STAT_TIME>'" + startTime + "' and STAT_TIME<'" + endTime +"'");
		logger.debug("batchInsert StatUser");
		final List<StatUser> list = statUserList;
		String sql = "INSERT INTO stat_user(_USER, CONN, DOWN_FILE, DOWN_MB, DOWN_PERCENT, UP_FILE, UP_MB, UP_PERCENT, STAT_TIME, CREATE_TIME)" 
			+ "VALUES (?,?,?,?,?,?,?,?,?,?)";
		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setString(1, list.get(i).getUser());
				ps.setInt(2, list.get(i).getConn());
				ps.setInt(3, list.get(i).getDownFile());
				ps.setDouble(4, list.get(i).getDownMb());
				ps.setDouble(5, list.get(i).getDownPercent());
				ps.setInt(6, list.get(i).getUpFile());
				ps.setDouble(7, list.get(i).getUpMb());
				ps.setDouble(8, list.get(i).getUpPercent());
				ps.setTimestamp(9, list.get(i).getStatTime());
				ps.setTimestamp(10, list.get(i).getCreateTime());
			}
			@Override
			public int getBatchSize() {
				return list.size();
			}
		};
		return jdbcTemplate.batchUpdate(sql, setter);
	}

}
